<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Moderators_mod extends CI_Model {

    private $_dbTableUsers = 'users';
    private $_dbTableImages = 'images';
    private $_dbTableTeams = 'teams';
    private $_dbTableMessages = 'messages';
    private $_dbTableActivity = 'mod_activity';
    private $_dbTableIns = 'mod_ins';
    
    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }
    
    
    public function getTeams($only_active=false)
    {
        $this->db->select("*")
                 ->from($this->_dbTableTeams);
        if( true == $only_active )
             $this->db->where('active',1);
        
         $query = $this->db->get();
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else { }

        if ($query->num_rows() > 0) {
          $result = $query->result();
          $query->free_result();
        } else {
          $result = NULL;
        }

        return $result;
    }
    
    
    
    public function getOneTeam($id,$like_array = false)
    {
        $query = $this->db->select('*')
                          ->from($this->_dbTableTeams)
                          ->where('id',$id)
                          ->get();
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else { }

        if ($query->num_rows() == 1) { // ONE ROW
          if( true == $like_array ) // 
            $result = $query->row_array();// RETURN ONE ROW ARRAY
          else
            $result = $query->row(); // RETURN ONE ROW object
          $query->free_result();
        } else {
          $result = NULL;
        }

        return $result;
    }            
    
    public function changeActiveTeam($id)      
    {
        $sql = "UPDATE {$this->_dbTableTeams} SET `active` = (`active` + 1) MOD 2 WHERE `id` = {$id};";
        
        $this->db->query($sql);

        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else { }

        if ($this->db->affected_rows() == 1)
            return true; // Uspesno upisano
        else
            return false; // Nesuspesan upis
                
    }
    
    public function saveTeam($data)
    {
        if (isset($data['id']) && $data['id'] != "") { // Update
            $this->db->where('id', $data['id'])->update($this->_dbTableTeams, $data);
        } else { // Insert           
            $this->db->insert($this->_dbTableTeams, $data);
        }

        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }
    }
    public function getConversations() {
        
        $sql = "
              SELECT * FROM
              (SELECT DISTINCT 
                       {$this->_dbTableMessages}.to,
                       {$this->_dbTableMessages}.from,
                       {$this->_dbTableMessages}.seen,
                       ( SELECT username  FROM {$this->_dbTableUsers} WHERE `id`=  {$this->_dbTableMessages}.`from` ) as username_from,
                       ( SELECT username  FROM {$this->_dbTableUsers} WHERE `id`=  {$this->_dbTableMessages}.`to` ) as username_to,    
                       ( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and `user_id`=  {$this->_dbTableMessages}.`from` ORDER BY main desc LIMIT 0,1 ) as image_name_from,
                       ( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and `user_id`=  {$this->_dbTableMessages}.`to` ORDER BY main desc LIMIT 0,1 ) as image_name_to   
                FROM  {$this->_dbTableMessages}, {$this->_dbTableUsers}
                WHERE 
                {$this->_dbTableMessages}.`to` IN (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.is_fake=1)
                     AND
                        {$this->_dbTableMessages}.seen=0
                      
                      AND
                      (                       
                        `del_to` = 0
                      OR  
                        `del_from` = 0 
                      )  
                      
                      
                ORDER BY {$this->_dbTableMessages}.`seen` ASC, UNIX_TIMESTAMP(`time`) DESC 
               ) AS sub                       
                
               
                ;
               ";
         $query = $this->db->query($sql);
         $result = array();
         if ($query->num_rows() > 0) {
          $conversations = $query->result();
          $query->free_result();
         
          $result = array();
          $row = new stdClass();
          foreach ($conversations as $conversation) {
          $row = $conversation;
          $query = $this->db->select(" COUNT(*) as num_messages ")
          ->from($this->_dbTableMessages)
          ->where('from = ' . $conversation->from)
          ->where('to = ' . $conversation->to)
          ->where($this->_dbTableMessages . ".seen = 0")
           ->get();
          //echo $this->db->last_query();
          $errors = $this->db->_error_message();
          if (!empty($errors)) {
          throw new Exception("SQL Error: " . $errors);
          } else {

          }

          if ($query->num_rows() > 0) {
          $count = $query->row();
          $row->num_messages = $count->num_messages;
          $query->free_result();
          } else {

          $row->num_messages = 0;
          }
          $result[] = $row;
          } 
           } 
          else {
          $result = NULL;
          }
        return $result;
    }
     public function getConversation($fromId, $toId) {

        $where = "( ({$this->_dbTableMessages}.to = {$toId} AND {$this->_dbTableMessages}.from = {$fromId}) AND `del_to` = 0 )
            OR  
            ( ({$this->_dbTableMessages}.to = {$fromId} AND {$this->_dbTableMessages}.from = {$toId}) AND `del_from` = 0 )";


       


        $this->db->select
                ("time, content, from, to, "
                . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `from`) as username_from, "
                . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `to` ) as username_to, "
                . "( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and {$this->_dbTableImages}.`user_id`= `from` ORDER BY main desc LIMIT 1) as image_name_from, "
                . "( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and {$this->_dbTableImages}.`user_id`= `to` ORDER BY main desc LIMIT 1) as image_name_to "
        );
        $this->db->from($this->_dbTableMessages);

        if ($where != "")
            $this->db->where($where);

        $this->db->order_by($this->_dbTableMessages . '.id', 'desc');

        $query = $this->db->get();

        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->result();
            $query->free_result();
        } else {
            $result = NULL;
        }
           
        return $result;
    }
    public function getUserData($fromId){
        $this->db->select("id,coins, zip_code, description");
        $this->db->from($this->_dbTableUsers);
        $this->db->where('id', $fromId);
        $query = $this->db->get();
         if ($query->num_rows() > 0) {
            $result = $query->row();
            $query->free_result();
        } else {
            $result = NULL;
        }
        return $result;
    } 
    public function getStatistic($filter = NULL) {
        $where = "";
        if (isset($filter['date_from']) && $filter['date_from'] != "") {
            if ($where != "") {
                $where .= " AND ";
            }
            $where .= "( ";
            $where .= $this->_dbTableIns . ".date >= '" . $filter['date_from'];
            $where .= "' )";
        }
        if (isset($filter['date_to']) && $filter['date_to'] != "") {
            if ($where != "") {
                $where .= " AND ";
            }
            $where .= "( " . $this->_dbTableIns . ".date <='" . $filter['date_to'];
            $where .= "' ) ";
        }
        if (isset($filter['team']) && $filter['team'] != "") {
            if ($where != "") {
                $where .= " AND ";
            }
            $where .= "( " . $this->_dbTableIns . ".mod_id IN (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`team_id` =".$filter['team'].") ";
            $where .= " ) ";
        }
        $this->db->select("DATE(date) as date");
        $this->db->from($this->_dbTableIns);
        if ($where != "")
            $this->db->where($where);
        $this->db->order_by('DATE(date)', 'desc');
        $this->db->group_by('DATE(date)');
        $query = $this->db->get();
        $dates = $query->result();
        $result = array();
        
        //$where = $this->_dbTableMessages . ".seen = '1'";

        foreach ($dates as $date) {
            $row = new stdClass();
            $row->date = $date->date;
            $row->mod_activity=$this->getActivity($date->date, $filter['team']);
            $result[] = $row;
        }

        return $result;
    }
    private function getActivity($date, $team=NULL){
        $where = "";
        if($team!=NULL&&$team!=''){
            $where .= "( " . $this->_dbTableIns . ".mod_id IN (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`team_id` =".$team.") ";
            $where .= " ) ";
        }
        $this->db->select("mod_id, outs, ins, "
        . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `mod_id`) as username_mod"
        //. "( SELECT name FROM {$this->_dbTableTeams} WHERE {$this->_dbTableTeams}.id = ??? ) as team "
        );
        $this->db->from($this->_dbTableIns);
        $this->db->where("DATE(" . $this->_dbTableIns . ".date) ='" . $date . "'");
         if ($where != "")
            $this->db->where($where);
        $query = $this->db->get();
        $mod_activity = $query->result();
        return $mod_activity;
        }

    private function getNumOut($date) {
        $this->db->from($this->_dbTableMessages);
        $this->db->where("DATE(" . $this->_dbTableMessages . ".time) ='" . $date . "' AND {$this->_dbTableMessages}.`from` IN (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.is_fake=1)");
        $num_out = $this->db->count_all_results();
        return $num_out;
    }
    private function getNumIn($date) {
        $this->db->from($this->_dbTableMessages);
        $this->db->where("DATE(" . $this->_dbTableMessages . ".time) ='" . $date . "' AND {$this->_dbTableMessages}.`to` IN (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.is_fake=1)");
        $num_in = $this->db->count_all_results();
        return $num_in;
    }
     private function getNumSeen($date) {
        $this->db->from($this->_dbTableMessages);
        $this->db->where("DATE(" . $this->_dbTableMessages . ".time) ='" . $date . "' AND {$this->_dbTableMessages}.`to` IN (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.is_fake=1) AND {$this->_dbTableMessages} .seen = '1'");
        $num_seen = $this->db->count_all_results();
        return $num_seen;
    }
     private function getNumIns($date) {
        $this->db->from($this->_dbTableMessages);
        $this->db->where("DATE(" . $this->_dbTableMessages . ".time) ='" . $date . "' AND {$this->_dbTableMessages}.`to` IN (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.is_fake=1) AND {$this->_dbTableMessages}.`to` IN (SELECT fake_user_id FROM {$this->_dbTableActivity} )");
        $num_ins = $this->db->count_all_results();
        return $num_ins;
    }
    
    
    

 }

 
 /*
  $query = $this->db->select('*')
  ->from()
  ->where()
  ->get();
  //echo $this->db->last_query();
  $errors = $this->db->_error_message();
  if (!empty($errors)) {
  throw new Exception("SQL Error: " . $errors);
  } else { }

  if ($query->num_rows() > 0) {
  $result = $query->result();
  $query->free_result();
  } else {
  $result = NULL;
  }

  return $result;
 */
?>